﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace SC.CodeBuilder
{
    /// <summary>
    /// 生成代码，每张表是一个实例
    /// </summary>
    partial class Builder : IDisposable
    {
        #region 字段
        //public static string ConnStr;
        /// <summary>
        /// 公共数据集
        /// </summary>
        readonly DataSet Columns;
        /// <summary>
        /// 要查询的表名
        /// </summary>
        readonly string tableName;
        /// <summary>
        /// 使用的命名空间
        /// </summary>
        readonly string unameSpace;
        /// <summary>
        /// 数据库访问类名
        /// </summary>
        readonly string sqlName;
        /// <summary>
        /// 自增字段
        /// </summary>
        readonly string identityName;
        /// <summary>
        /// 标识是否有主键
        /// </summary>
        readonly bool hasidentity = false;
        /// <summary>
        /// 标识类型
        /// </summary>
        readonly string identityType;

        /// <summary>
        /// 比较代码组合
        /// </summary>
        public int Choice { get; set; }

        bool Add;
        bool Update;
        bool Delete;
        bool GetModel;
        bool GetList;
        bool Exists;
        bool GetMaxID;
        bool GetRecordCount;
        bool Row2Model;
        bool GetIDAL;
        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="_tablename">表名</param>
        /// <param name="_unameSpace">命名空间</param>
        /// <param name="_sqlname">数据库访问类名</param>
        /// <param name="_choice">比较代码组合</param>
        /// <param name="Add">是否生成Add方法(增加数据)</param>
        /// <param name="Update">是否生成Update方法(修改数据)</param>
        /// <param name="Delete">是否生成Delete方法(删除数据)</param>
        /// <param name="GetModel">是否生成GetModel方法(取得对象)</param>
        /// <param name="GetList">是否生成GetList方法(取得对象列表)</param>
        /// <param name="Exists">是否生成Exists方法(查询是否存在)</param>
        /// <param name="GetMaxID">是否生成GetMaxID方法()</param>
        /// <param name="GetRecordCount">是否生成GetRecordCount方法(查询总行数)</param>
        /// <param name="Row2Model">是否生成Row2Model方法(DataRow数据转模型类方法)</param>
        /// <param name="GetIDAL">是否生成IDAL(接口层)</param>
        public Builder(
            string _tablename,
            string _unameSpace,
            string _sqlname,
            int _choice,
            bool Add,
            bool Update,
            bool Delete,
            bool GetModel,
            bool GetList,
            bool Exists,
            bool GetMaxID,
            bool GetRecordCount,
            bool Row2Model,
            bool GetIDAL
            )
        {
            this.Add = Add;
            this.Update = Update;
            this.Delete = Delete;
            this.GetModel = GetModel;
            this.GetList = GetList;
            this.Exists = Exists;
            this.GetMaxID = GetMaxID;
            this.GetRecordCount = GetRecordCount;
            this.Row2Model = Row2Model;
            this.GetIDAL = GetIDAL;


            this.tableName = _tablename;
            this.unameSpace = string.IsNullOrWhiteSpace(unameSpace) ? null : (unameSpace + ".");
            this.sqlName = string.IsNullOrWhiteSpace(_sqlname) ? "SqlHelper" : _sqlname;
            this.Choice = _choice;
            Columns = SqlHelper.ExecuteDataSet(CommandType.Text, "sp_columns " + tableName);
            foreach (DataRow dr in Columns.Tables[0].Rows)
            {
                if (dr[5].ToString() == "int identity")
                {
                    identityName = dr[3].ToString();
                    identityType = sqlTypeToNetType(dr[5].ToString());
                    hasidentity = true;
                    break;
                }
            }
            if (!hasidentity)
            {
                DataSet st = SqlHelper.ExecuteDataSet(CommandType.Text, "EXEC sp_pkeys @table_name='" + tableName + "'");
                if (st.Tables.Count > 0)
                {
                    if (st.Tables[0].Rows.Count > 0)
                    {
                        identityName = st.Tables[0].Rows[0][3].ToString();
                        foreach (DataRow item in Columns.Tables[0].Rows)
                        {
                            if (item[3].ToString() == identityName)
                            {
                                identityType = sqlTypeToNetType(item[5].ToString());
                                hasidentity = true;
                                break;
                            }
                        }
                    }
                }
            }
        }
        #endregion
        #region 数据类型字典
        /// <summary>
        /// 找到数据库数据类型对应的Net数据类型
        /// </summary>
        string sqlTypeToNetType(string inname)
        {
            switch (inname)
            {
                case "int identity":
                case "int":
                case "bigint":
                case "smallint":
                case "tinyint": return "int";

                case "char":
                case "nchar":
                case "varchar":
                case "nvarchar":
                case "text":
                case "ntext": return "string";

                case "datetime":
                case "smalldatetime": return "DateTime";

                case "numeric":
                case "real": return "double";

                case "binary":
                case "image":
                case "varbinary": return "byte[]";

                case "float":
                case "money": return "float";

                case "decimal": return "decimal";
                case "bit": return "bool";
                case "smallmoney": return "decimal";
                case "uniqueidentifier": return "Guid";
                default: return inname;
            }
        }
        /// <summary>
        /// 找到数据库数据类型对应的SqlDbType数据类型
        /// </summary>
        string sqlTypeToSqlDbType(string inname)
        {
            switch (inname)
            {
                case "bigint": return "BigInt";
                case "binary": return "Binary";
                case "bit": return "Bit";
                case "char": return "Char";
                case "datetime": return "DateTime";
                case "decimal": return "Decimal";
                case "float": return "Float";
                case "image": return "Image";
                case "int": return "Int";
                case "money": return "Money";
                case "nchar": return "NChar";
                case "ntext": return "NText";
                case "nvarchar": return "NVarChar";
                case "real": return "Real";
                case "uniqueidentifier": return "UniqueIdentifier";
                case "smalldatetime": return "SmallDateTime";
                case "smallint": return "SmallInt";
                case "smallmoney ": return "SmallMoney";
                case "text": return "Text";
                case "timestamp": return "Timestamp";
                case "tinyint": return "TinyInt";
                case "varbinary": return "VarBinary";
                case "varchar": return "VarChar";
                case "variant": return "Variant";
                case "xml": return "Xml";
                case "udt": return "Udt";
                case "structured": return "Structured";
                case "date": return "Date";
                case "time": return "Time";
                case "datetime2": return "DateTime2";
                case "datetimeoffset": return "DateTimeOffset";

                case "int identity": return "Int";
                default: return "NVarChar";
            }
        }
        #endregion
        #region 拼接字段
        /// <summary>
        /// 生成 字段名,字段名,字段名,字段名...
        /// </summary>
        /// <param name="Columns"></param>
        /// <returns></returns>
        StringBuilder JoinDr(StringBuilder sb, bool hasIdentity)
        {
            List<string> list = new List<string>();
            foreach (DataRow dr in Columns.Tables[0].Rows)
            {
                if (dr[5].ToString().Contains("identity") && !hasIdentity) { continue; }
                list.Add(dr[3].ToString());
            }
            sb.Append(string.Join(",", list.ToArray()));
            return sb;
        }
        /// <summary>
        /// 生成 @字段名,@字段名,@字段名,@字段名...
        /// </summary>
        /// <param name="Columns"></param>
        /// <returns></returns>
        StringBuilder JoinDrWithAt(StringBuilder sb, bool hasIdentity)
        {
            List<string> list = new List<string>();
            foreach (DataRow dr in Columns.Tables[0].Rows)
            {
                if (dr[5].ToString().Contains("identity") && !hasIdentity) { continue; }
                list.Add("@" + dr[3]);
            }
            sb.Append(string.Join(",", list.ToArray()));
            return sb;
        }
        /// <summary>
        /// 生成 字段名=@字段名,字段名=@字段名,字段名=@字段名,字段名=@字段名...
        /// </summary>
        /// <param name="sb"></param>
        StringBuilder JoinDrWithParameter(StringBuilder sb, bool hasIdentity)
        {
            List<string> list = new List<string>();

            foreach (DataRow dr in Columns.Tables[0].Rows)
            {
                if (dr[5].ToString().Contains("identity") && !hasIdentity) { continue; }
                list.Add(dr[3] + "=@" + dr[3]);
            }

            sb.Append(string.Join(", ", list.ToArray()));
            return sb;
        }
        /// <summary>
        /// 生成 Type 字段名,Type 字段名,Type 字段名,Type 字段名...
        /// </summary>
        /// <param name="sb"></param>
        StringBuilder JoinDrWithType(StringBuilder sb, bool hasIdentity)
        {
            List<string> list = new List<string>();

            foreach (DataRow dr in Columns.Tables[0].Rows)
            {
                if (dr[5].ToString().Contains("identity") && !hasIdentity) { continue; }
                list.Add(sqlTypeToNetType(dr[5].ToString()) + " " + dr[3]);
            }
            sb.Append(string.Join(", ", list.ToArray()));
            return sb;
        }
        #endregion
        #region 拼接SqlParameter
        /// <summary>
        /// 生成 SqlParameter添加的字符串
        /// new SqlParameter("@id" SqlType.Int,4){Value=model.Name},
        /// new SqlParameter("@id" SqlType.Int,4){Value=model.Name},
        /// </summary>
        /// <param name="Columns"></param>
        /// <returns></returns>
        StringBuilder JoinSqlParameter(StringBuilder sb, bool hasIdentity)
        {
            List<string> list = new List<string>();

            foreach (DataRow dr in Columns.Tables[0].Rows)
            {
                if (dr[5].ToString().Contains("identity") && !hasIdentity) { continue; }
                list.Add("                 new SqlParameter(\"@" + dr[3].ToString() + "\", SqlDbType." + sqlTypeToSqlDbType(dr[5].ToString()) + ", " + dr[7].ToString() + ") { Value = model." + UpperLetter(dr[3].ToString()) + " }");
            }
            sb.AppendLine(string.Join("," + Environment.NewLine, list.ToArray()));
            return sb;
        }
        /// <summary>
        /// 生成 SqlParameter添加的字符串 不用Model
        /// new SqlParameter("@id" SqlType.Int,4){Value=model.Name},
        /// new SqlParameter("@id" SqlType.Int,4){Value=model.Name},
        /// </summary>
        /// <param name="Columns"></param>
        /// <returns></returns>
        StringBuilder JoinSqlParameterNoModel(StringBuilder sb, bool hasIdentity)
        {
            List<string> list = new List<string>();

            foreach (DataRow dr in Columns.Tables[0].Rows)
            {
                if (dr[5].ToString().Contains("identity") && !hasIdentity) { continue; }
                list.Add("                 new SqlParameter(\"@" + dr[3].ToString() + "\",SqlDbType." + sqlTypeToSqlDbType(dr[5].ToString()) + "," + dr[7].ToString() + ") { Value = " + UpperLetter(dr[3].ToString()) + " }");
            }
            sb.AppendLine(string.Join("," + Environment.NewLine, list.ToArray()));
            return sb;
        }
        #endregion

        #region 取得所有数据库 - static List<string> GetDataBases()
        /// <summary>
        /// 取得所有数据表
        /// </summary>
        /// <returns></returns>
        internal static List<string> GetDataBases()
        {
            List<string> list = new List<string>();
            DataSet st = SqlHelper.ExecuteDataSet(CommandType.Text, "EXEC sys.sp_databases");
            foreach (DataRow dr in st.Tables[0].Rows)
            {
                string tname = dr[0].ToString();
                if (tname != "master" && tname != "model" && tname != "msdb" && tname != "tempdb")
                {
                    list.Add(tname);
                }
            }
            return list;
        }
        #endregion
        #region 取得所有数据表 - static List<string> GetTables()
        /// <summary>
        /// 取得所有数据表
        /// </summary>
        /// <returns></returns>
        internal static List<string> GetTables(string _name)
        {
            List<string> list = new List<string>();
            DataSet st = SqlHelper.ExecuteDataSet(CommandType.Text, "sp_tables  null,'dbo'");
            foreach (DataRow dr in st.Tables[0].Rows) { list.Add(dr[2].ToString()); }
            return list;
        }
        #endregion

        #region 将传入的字符串首字母大写 + static string UpperLetter(string str)
        /// <summary>
        /// 将传入的字符串首字母大写
        /// </summary>
        /// <param name="str">字符串</param>
        /// <returns></returns>
        internal static string UpperLetter(string str)
        {
            if (str.Length > 1) { return str[0].ToString().ToUpper() + str.Substring(1, str.Length - 1); }
            else { return str.ToUpper(); }
        }
        #endregion

        #region 实现IDisposable
        /// <summary>
        /// 实现IDisposable
        /// </summary>
        public void Dispose()
        {
            Columns.Dispose();
            //keys.Clear();
        }
        #endregion
    }
}
